{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fa3cbfcf",
   "metadata": {},
   "source": [
    "# 回顾"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "337c6f9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "deployment=\"gpt4\" #在azure中的deployment name\n",
    "model=\"gpt-4\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4b7a217c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from openai import AzureOpenAI\n",
    "    \n",
    "client = AzureOpenAI(\n",
    "    api_key=os.getenv(\"OPENAI_API_KEY\"),  \n",
    "    api_version=os.getenv(\"OPENAI_API_VERSION\"),\n",
    "    azure_endpoint = os.getenv(\"OPENAI_API_BASE\")\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "99c60769",
   "metadata": {},
   "outputs": [],
   "source": [
    "def translate(text):\n",
    "    messages = []\n",
    "    messages.append( {\"role\": \"system\", \n",
    "                      \"content\": \"You are a translator. Please, translate the user's request to English.\"})\n",
    "    messages.append( {\"role\": \"user\", \"content\": text})\n",
    "    response = client.chat.completions.create(\n",
    "        model=deployment,\n",
    "        messages=messages,\n",
    "        temperature=0.5,\n",
    "        max_tokens = 100\n",
    "    )\n",
    "    return response.choices[0].message.content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e2c83b7f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Encoding 'cl100k_base'>\n",
      "chinese:在未来还没有到来的时候，总要有人把它创造出来，那个人应该是我们。 ; 35 tokens\n",
      "\n",
      "english:Before the future arrives, there always has to be someone to create it, and that person should be us. ; 22 tokens\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import openai\n",
    "import tiktoken\n",
    "\n",
    "encoding = tiktoken.encoding_for_model(\"gpt-4\")\n",
    "\n",
    "print(encoding)\n",
    "\n",
    "chinese = \"\"\"在未来还没有到来的时候，总要有人把它创造出来，那个人应该是我们。\"\"\"\n",
    "english = translate(chinese)\n",
    "\n",
    "num_of_tokens_in_chinese = len(encoding.encode(chinese))\n",
    "num_of_tokens_in_english = len(encoding.encode(english))\n",
    "\n",
    "print(f\"chinese:{chinese} ; {num_of_tokens_in_chinese} tokens\\n\")\n",
    "print(f\"english:{english} ; {num_of_tokens_in_english} tokens\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9daae292",
   "metadata": {},
   "source": [
    "# 常见应用场景"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f8773d0",
   "metadata": {},
   "source": [
    "## 意图识别"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "12b4c0a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from openai import AzureOpenAI\n",
    "    \n",
    "client = AzureOpenAI(\n",
    "    api_key=os.getenv(\"OPENAI_API_KEY\"),  \n",
    "    api_version=os.getenv(\"OPENAI_API_VERSION\"),\n",
    "    azure_endpoint = os.getenv(\"OPENAI_API_BASE\")\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f0713ebf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "用户的意图是设置一个提醒，以便在明天早上8点时提醒他们有一个会议。\n"
     ]
    }
   ],
   "source": [
    "import openai\n",
    "response = client.chat.completions.create(\n",
    "    # 如果直接访问OpenAI GPT服务的同学，这里不要使用engine这个参数，要使用model，如： model=“gpt-4”\n",
    "    model=deployment,\n",
    "    temperature = 0,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": \"\"\"\n",
    "          Recognize the intent from the user's input \n",
    "         \"\"\"},\n",
    "        #{\"role\": \"user\", \"content\": \"订明天早5点北京到上海的飞机\"}\n",
    "        {\"role\": \"user\", \"content\": \"提醒我明早8点有会议\"}\n",
    "    ]\n",
    "  )\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "786389c2",
   "metadata": {},
   "source": [
    "## 生成SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ce8e1807",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "为了计算所有学生英语课程的平均成绩，我们需要先找到英语课程对应的`course_id`，然后使用这个`course_id`去`students`表中筛选出所有选修了英语课程的学生，最后计算这些学生的成绩平均值。\n",
      "\n",
      "以下是一个可能的SQL查询语句，用于计算英语课程的平均成绩：\n",
      "\n",
      "```sql\n",
      "SELECT AVG(s.score) AS average_score\n",
      "FROM students s\n",
      "JOIN courses c ON s.course_id = c.id\n",
      "WHERE c.name = 'English';\n",
      "```\n",
      "\n",
      "这个查询语句做了以下几件事情：\n",
      "\n",
      "1. 使用`JOIN`语句将`students`表和`courses`表连接起来，基于`course_id`和`id`字段的匹配。\n",
      "2. 在`WHERE`子句中指定了课程名称为'English'，以筛选出所有英语课程的记录。\n",
      "3. 使用`AVG`函数计算所有筛选出来的学生的成绩平均值，并将其命名为`average_score`。\n",
      "\n",
      "执行这个查询后，你将得到所有学生英语课程的平均成绩。\n"
     ]
    }
   ],
   "source": [
    "import openai, os\n",
    "from langchain.llms import OpenAI\n",
    "\n",
    "system_prompt =  \"\"\"  You are a software engineer, you can anwser the user request based on the given tables:\n",
    "                  table “students“ with the columns [id, name, course_id, score] \n",
    "                  table \"courses\" with the columns [id, name] \n",
    "                  \"\"\"\n",
    "\n",
    "prompt = system_prompt\n",
    "\n",
    "response = client.chat.completions.create(\n",
    "    model=deployment,\n",
    "    temperature = 0,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": prompt},\n",
    "        {\"role\": \"user\", \"content\": \"计算所有学生英语课程的平均成绩\"},\n",
    "    ],\n",
    "    max_tokens = 500\n",
    "  )\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c6ea8d7",
   "metadata": {},
   "source": [
    "## 生成代码友好的提示词"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03ff7a80",
   "metadata": {},
   "source": [
    "### 规范输出的格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a327cd24",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "```json\n",
      "{\n",
      "  \"intention\": \"set_reminder\",\n",
      "  \"parameters\": {\n",
      "    \"time\": \"8:00\",\n",
      "    \"date\": \"tomorrow\",\n",
      "    \"event\": \"会议\"\n",
      "  }\n",
      "}\n",
      "```\n"
     ]
    }
   ],
   "source": [
    "import openai\n",
    "response = client.chat.completions.create(\n",
    "    model=deployment,\n",
    "    temperature = 0,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": \"\"\"\n",
    "          Recognize the intent from the user's input and format output as JSON string. \n",
    "        The output JSON string includes: \"intention\", \"paramters\" \"\"\"},\n",
    "        {\"role\": \"user\", \"content\": \"提醒我明早8点有会议\"}\n",
    "    ]\n",
    "  )\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e9ddc0b4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sure, to list the students who scored more than 80 in the English course, you would need to join the `students` table with the `courses` table where the `course_id` matches the `id` of the English course, and then filter the results based on the score. Here is the SQL string that would accomplish this:\n",
      "\n",
      "```sql\n",
      "SELECT s.id, s.name, s.course_id, s.score\n",
      "FROM students s\n",
      "JOIN courses c ON s.course_id = c.id\n",
      "WHERE c.name = 'English' AND s.score > 80;\n",
      "```\n"
     ]
    }
   ],
   "source": [
    "import openai, os\n",
    "from langchain.llms import OpenAI\n",
    "\n",
    "system_prompt =  \"\"\"  You are a software engineer, you can write a SQL string as the anwser according to the user request \n",
    "               The user's requirement is based on the given tables:\n",
    "                  table “students“ with the columns [id, name, course_id, score];\n",
    "                  table \"courses\" with the columns [id, name].\"\"\"\n",
    "\n",
    "prompt = system_prompt\n",
    "\n",
    "response = client.chat.completions.create(\n",
    "    model=deployment,\n",
    "    temperature = 0,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": prompt},\n",
    "        #{\"role\": \"user\", \"content\": \"列出英语成绩大于80分的学生\"},\n",
    "        {\"role\": \"user\", \"content\": \"列出英语课程成绩大于80分的学生, 返回结果只包括SQL\"},\n",
    "        #{\"role\": \"user\", \"content\": \"列出年龄大于13的学生\"}\n",
    "    ],\n",
    "    max_tokens = 500\n",
    "  )\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "446523a7",
   "metadata": {},
   "source": [
    "### 文本规范异常输出的格式 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "17cfa279",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-1\n"
     ]
    }
   ],
   "source": [
    "import openai, os\n",
    "from langchain.llms import OpenAI\n",
    "\n",
    "system_prompt =  \"\"\"  You are a software engineer, you can write a SQL string as the anwser according to the user request \n",
    "               The user's requirement is based on the given tables:\n",
    "                  table “students“ with the columns [id, name, course_id, score];\n",
    "                  table \"courses\" with the columns [id, name].\"\"\"\n",
    "\n",
    "system_prompt_with_negative =  \"\"\"  \n",
    "You are a software engineer, you can write a SQL string as the anwser according to the user request.\n",
    "Also, when you cannot create the SQL query for the user's request based on the given tables, please, only return \"invalid request\"\n",
    "               The user's requirement is based on the given tables:\n",
    "                  table “students“ with the columns [id, name, course_id, score];\n",
    "                  table \"courses\" with the columns [id, name].\"\"\"\n",
    "\n",
    "#prompt = system_prompt\n",
    "prompt = system_prompt_with_negative\n",
    "\n",
    "response = openai.ChatCompletion.create(\n",
    "    engine=deployment,\n",
    "    model=model,\n",
    "    temperature = 0,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": prompt},\n",
    "        {\"role\": \"user\", \"content\": \"列出年龄大于13的学生\"}\n",
    "    ],\n",
    "    max_tokens = 500\n",
    "  )\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2bac7b0c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31f3122c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
